<?php

class Application_Model_Pdo {

    public function obrir_bd() {
        $hostname = 'localhost';
        $username = 'root';
        $password = '';
        try {
            $sth = new PDO("mysql:host=$hostname;dbname=pract_uf3", $username, $password);
            /*             * * echo a message saying we have connected ** */
            return $sth;
        } catch (PDOException $e) {
            echo $e->getMessage();
        }
    }

    public function tancar_bd($sth) {
        $sth = null;
    }

    /*     * ******************************* DEPARTAMENTS *********************************** */

    public function getDept($id_dept) {
        $link = $this->obrir_bd();
        $sql = "SELECT * FROM dept where id=:idDept";
        $st = $link->prepare($sql);
        $st->bindParam(':idDept', $id_dept, PDO::PARAM_INT);
        $st->execute();
        $dept = $st->fetch(PDO::FETCH_ASSOC);
        $this->tancar_bd($link);
        return $dept;
    }

    public function addDept($n_dept, $d_dept) {
        $link = $this->obrir_bd();
        $sql = "INSERT INTO dept (nom,descripcio) VALUES (:nom,:dscp)";
        $st = $link->prepare($sql);
        $st->bindParam(':nom', $n_dept, PDO::PARAM_STR);
        $st->bindParam(':dscp', $d_dept, PDO::PARAM_STR);
        $st->execute();
        $this->tancar_bd($link);
    }

    public function editDept($id_dept, $n_dept, $d_dept) {
        $link = $this->obrir_bd();
        $sql = "UPDATE dept SET nom = :nom , descripcio = :dsc WHERE id=:idDept";
        $st = $link->prepare($sql);
        $st->bindParam(':idDept', $id_dept, PDO::PARAM_INT);
        $st->bindParam(':nom', $n_dept, PDO::PARAM_STR);
        $st->bindParam(':dsc', $d_dept, PDO::PARAM_STR);
        $st->execute();
        $this->tancar_bd($link);
    }

    public function deleteDept($id_dept) {
        $link = $this->obrir_bd();
        try {
            $link->beginTransaction();
            //esborrar empleats d'aquell dept
            $sql = "DELETE FROM emp WHERE id_dept=:idDept";
            $st = $link->prepare($sql);
            $st->bindParam(':idDept', $id_dept, PDO::PARAM_INT);
            $st->execute();

            //esborrar dept
            $sql = "DELETE FROM dept WHERE id=:idDept";
            $st = $link->prepare($sql);
            $st->bindParam(':idDept', $id_dept, PDO::PARAM_INT);
            $st->execute();
            $link->commit();
        } catch (PDOException $e) {
            $link->rollback();
        }
        $this->tancar_bd($link);
    }

    public function listDepts() {
        $link = $this->obrir_bd();
        $sql = "SELECT * FROM dept";
        $i = 0;
        foreach ($link->query($sql) as $row) {
            $depts[$i] = $row;
            $i++;
        }
        $this->tancar_bd($link);
        if (!$depts)
            return null;
        return $depts;
    }

    public function listDeptsSelect() {
        $link = $this->obrir_bd();
        $sql = "SELECT id,nom FROM dept";
        $q = $link->query($sql);
        $q->setFetchMode(PDO::FETCH_ASSOC);
        $depts = array();
        while ($row = $q->fetch()) {
            $depts[$row['id']] = $row['nom'];
        }
        $this->tancar_bd($link);
        return $depts;
    }

    /*     * ******************************* EMPS *********************************** */

    public function getEmp($id_emp) {
        $link = $this->obrir_bd();
        $sql = "SELECT id,nom,cognom,dni,id_dept as departament FROM emp where id=:idEmp";
        $st = $link->prepare($sql);
        $st->setFetchMode(PDO::FETCH_ASSOC);
        $st->bindParam(':idEmp', $id_emp, PDO::PARAM_INT);
        $st->execute();
        $this->tancar_bd($link);
        return $st->fetch();
    }

    public function addEmp($nom_e, $cognom_e, $dni_e, $id_d) {
        $link = $this->obrir_bd();
        $sql = "INSERT INTO emp (nom,cognom,dni,id_dept) VALUES (:nomEmp,:cognomEmp,:dniEmp,:idDept)";
        $st = $link->prepare($sql);
        $st->bindParam(':nomEmp', $nom_e, PDO::PARAM_STR);
        $st->bindParam(':cognomEmp', $cognom_e, PDO::PARAM_STR);
        $st->bindParam(':dniEmp', $dni_e, PDO::PARAM_STR);
        $st->bindParam(':idDept', $id_d, PDO::PARAM_INT);
        $st->execute();
        $this->tancar_bd($link);
    }

    public function editEmp($id_e, $nom_e, $cognom_e, $dni_e, $id_d) {
        $link = $this->obrir_bd();
        $sql = "UPDATE emp SET nom = :nomEmp , cognom = :cognomEmp,dni=:dniEmp,id_dept=:idDept WHERE id=:idEmp";
        $st = $link->prepare($sql);
        $st->bindParam(':nomEmp', $nom_e, PDO::PARAM_STR);
        $st->bindParam(':cognomEmp', $cognom_e, PDO::PARAM_STR);
        $st->bindParam(':dniEmp', $dni_e, PDO::PARAM_STR);
        $st->bindParam(':idDept', $id_d, PDO::PARAM_INT);
        $st->bindParam(':idEmp', $id_e, PDO::PARAM_INT);
        $st->execute();
        $this->tancar_bd($link);
    }

    public function deleteEmp($id_e) {
        $link = $this->obrir_bd();
        $sql = "DELETE FROM emp WHERE id=:idEmp";
        $st = $link->prepare($sql);
        $st->bindParam(':idEmp', $id_e, PDO::PARAM_INT);
        $st->execute();
        $this->tancar_bd($link);
    }

    public function listEmps() {
        $link = $this->obrir_bd();
        $sql = "SELECT e.id,e.nom,e.cognom,e.dni,d.nom as departament FROM emp e, dept d WHERE e.id_dept=d.id";
        $q = $link->query($sql);
        $q->setFetchMode(PDO::FETCH_ASSOC);
        $i = 0;
        $emps = array();
        while ($row = $q->fetch()) {
            $emps[$i] = $row;
            $i++;
        }
        $this->tancar_bd($link);
        return $emps;
    }

    public function listEmpsOrd($camp, $ord) {
        $link = $this->obrir_bd();
        $sql = "SELECT e.id,e.nom,e.cognom,e.dni,d.nom as departament FROM emp e, dept d WHERE e.id_dept=d.id order by $camp $ord";
        $stmt = $link->prepare($sql);
        //$stmt->bindParam(':camp', $camp);
        //$stmt->bindParam(':ord', $ord);
        $stmt->execute();
        $i = 0;
        $emps = array();
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $emps[$i] = $row;
            $i++;
        }
        $this->tancar_bd($link);
        return $emps;
    }

}

